const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔍 分析实例表结构和字段整合规则');
console.log('=====================================');

try {
  // 1. 分析目标实例表结构
  const targetTable = 'device_instances_piping_cooling_water_spray_system';
  console.log(`\n📋 分析目标实例表: ${targetTable}`);
  
  const tableExists = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name = ?
  `).get(targetTable);
  
  if (!tableExists) {
    console.log('❌ 目标实例表不存在');
    return;
  }
  
  // 获取实例表结构
  const instanceTableInfo = db.prepare(`PRAGMA table_info(${targetTable})`).all();
  console.log(`\n📊 实例表字段结构 (共${instanceTableInfo.length}个字段):`);
  
  // 分类字段
  const systemFields = ['id', 'created_at', 'updated_at'];
  const dataFields = instanceTableInfo.filter(col => !systemFields.includes(col.name));
  
  console.log(`  系统字段: ${systemFields.length}个`);
  systemFields.forEach(field => {
    const col = instanceTableInfo.find(c => c.name === field);
    if (col) console.log(`    ${col.name} (${col.type})`);
  });
  
  console.log(`  数据字段: ${dataFields.length}个`);
  dataFields.forEach((col, index) => {
    console.log(`    ${index + 1}. ${col.name} (${col.type})`);
  });
  
  // 2. 查找对应的设备类型映射
  console.log('\n🔍 查找设备类型映射:');
  const deviceMappings = db.prepare(`
    SELECT * FROM device_type_mappings 
    WHERE classification_name LIKE '%冷却水喷淋%' 
       OR classification_name LIKE '%喷淋%'
       OR english_name LIKE '%cooling%water%spray%'
  `).all();
  
  if (deviceMappings.length === 0) {
    console.log('❌ 未找到对应的设备类型映射');
    // 尝试从表名推断
    console.log('🔍 从表名推断设备类型...');
    const allMappings = db.prepare(`
      SELECT classification_name, naming_table, position_table, base_params_table 
      FROM device_type_mappings 
      WHERE specialty = 'piping'
    `).all();
    
    console.log('📋 配管专业所有设备类型:');
    allMappings.forEach(mapping => {
      console.log(`  ${mapping.classification_name}: ${mapping.base_params_table}`);
    });
  } else {
    console.log('✅ 找到设备类型映射:');
    deviceMappings.forEach(mapping => {
      console.log(`  设备: ${mapping.classification_name}`);
      console.log(`  命名表: ${mapping.naming_table}`);
      console.log(`  位置表: ${mapping.position_table}`);
      console.log(`  基础参数表: ${mapping.base_params_table}`);
    });
  }
  
  // 3. 根据表名推断可能的参数表组合
  console.log('\n💡 推断参数表组合:');
  // 从表名 device_instances_piping_cooling_water_spray_system 推断
  // piping = 配管专业
  // cooling_water_spray_system = 冷却水喷淋装置 = table_c494
  
  const potentialBaseTables = ['table_c494']; // 冷却水喷淋装置基础参数表
  const namingTable = 'table_c2';  // 标准命名表
  const positionTable = 'table_c4'; // 标准位置表
  
  console.log('推断的三个属性表:');
  console.log(`  命名表: ${namingTable}`);
  console.log(`  位置表: ${positionTable}`);
  console.log(`  基础参数表: ${potentialBaseTables.join(', ')}`);
  
  // 4. 获取三个属性表的字段
  console.log('\n📋 获取三个属性表的字段结构:');
  
  const attributeTables = [
    {name: namingTable, type: '命名表'},
    {name: positionTable, type: '位置表'},
    {name: potentialBaseTables[0], type: '基础参数表'}
  ];
  
  let totalExpectedFields = 0;
  const allAttributeFields = [];
  
  attributeTables.forEach(table => {
    try {
      const tableInfo = db.prepare(`PRAGMA table_info(${table.name})`).all();
      const fields = tableInfo.filter(col => !systemFields.includes(col.name));
      
      console.log(`\n  ${table.type} (${table.name}): ${fields.length}个字段`);
      fields.forEach((col, index) => {
        console.log(`    ${index + 1}. ${col.name} (${col.type})`);
        allAttributeFields.push({
          tableName: table.name,
          tableType: table.type,
          fieldName: col.name,
          fieldType: col.type
        });
      });
      
      totalExpectedFields += fields.length;
    } catch (error) {
      console.log(`    ❌ 无法获取${table.type}结构: ${error.message}`);
    }
  });
  
  // 5. 对比分析
  console.log('\n📊 实例表字段整合分析:');
  console.log(`  属性表总字段数: ${totalExpectedFields}`);
  console.log(`  实例表数据字段数: ${dataFields.length}`);
  console.log(`  字段匹配度: ${dataFields.length === totalExpectedFields ? '✅ 完全匹配' : '⚠️ 不匹配'}`);
  
  if (dataFields.length !== totalExpectedFields) {
    console.log(`  差异: ${Math.abs(dataFields.length - totalExpectedFields)}个字段`);
  }
  
  // 6. 逐字段匹配验证
  console.log('\n🔍 字段匹配验证:');
  const instanceFieldNames = dataFields.map(f => f.name);
  const attributeFieldNames = allAttributeFields.map(f => f.fieldName);
  
  let matchCount = 0;
  attributeFieldNames.forEach(fieldName => {
    const inInstance = instanceFieldNames.includes(fieldName);
    if (inInstance) matchCount++;
    console.log(`  ${fieldName}: ${inInstance ? '✅' : '❌'}`);
  });
  
  console.log(`\n📈 匹配统计:`);
  console.log(`  匹配字段: ${matchCount}/${attributeFieldNames.length}`);
  console.log(`  匹配率: ${(matchCount / attributeFieldNames.length * 100).toFixed(1)}%`);
  
  // 7. 查找未匹配的字段
  const unmatchedInInstance = instanceFieldNames.filter(name => !attributeFieldNames.includes(name));
  const unmatchedInAttributes = attributeFieldNames.filter(name => !instanceFieldNames.includes(name));
  
  if (unmatchedInInstance.length > 0) {
    console.log(`\n⚠️  实例表中多出的字段 (${unmatchedInInstance.length}个):`);
    unmatchedInInstance.forEach(field => console.log(`    + ${field}`));
  }
  
  if (unmatchedInAttributes.length > 0) {
    console.log(`\n❌ 属性表中缺失的字段 (${unmatchedInAttributes.length}个):`);
    unmatchedInAttributes.forEach(field => console.log(`    - ${field}`));
  }
  
  if (matchCount === attributeFieldNames.length && unmatchedInInstance.length === 0) {
    console.log('\n🎉 实例表字段整合完美！');
    console.log('   ✅ 所有属性表字段都正确整合到实例表');
    console.log('   ✅ 没有多余或缺失的字段');
  }
  
  // 8. 显示实例表创建模式
  console.log('\n📐 实例表创建模式总结:');
  console.log('实例表 = 系统字段 + 命名表字段 + 位置表字段 + 基础参数表字段');
  console.log(`${targetTable} = [id, created_at, updated_at] + [${namingTable}字段] + [${positionTable}字段] + [${potentialBaseTables[0]}字段]`);
  
} catch (error) {
  console.error('❌ 分析失败:', error.message);
} finally {
  db.close();
}